 *******************
* Exporting table for Global South COVID paper
* 
* last updated: 2020-06-23
* Magdalena Larreboure, Elliott Collins
*******************

// use "../data/01_TableOutcomes_COL.dta" , clear
// merge 1:1 recovr_id using "../data/01_COL_w1_weighted.dta", keepusing(LSMS_WEIGHT women_weight dpto_weight)
// rename _merge merge_weights

// gen paid_feb   = inc_wrk_feb=="Yes" if inc_wrk_feb!=""
// gen paid_now   = inc_wrk_now=="Yes" if inc_wrk_now!=""
// gen employmentdrop  = paid_feb==1 & paid_now==0 if paid_now!=.

// gen fsec_meals01=fsec_meals>0
// gen fsec_portions01=fsec_portions>0
// gen incomedrop     = work_less_earn | work_lost

// gen healthaccess   = hlth4
// gen missedmeals    = fsec_meals01 | fsec_portions01
// gen anysupport     = new_transfers
// gen accessmarkets  = fsec_access

// *gen poor=isPoor=="Poor"
// g poor = isPoor=="Pobre"

// keep incomedrop employmentdrop accessmarkets healthaccess missedmeals anysupport poor LSMS_WEIGHT

// save "../data/COL_maintable.dta" , replace

 glo dir ".."
 glo dr "$dir/rawdata"
 glo da "$dir/data"
 glo do "$dir/code"
 glo dtab "$dir/results"
 glo dfig "$dir/results" 

set more off

use "$da/COL_maintable.dta" , clear

gl weight LSMS_WEIGHT

loc thissample colombia

loc outcomes incomedrop employmentdrop accessmarkets healthaccess missedmeals anysupport incomeincr

loc heterogeneity poor

********************************************************************************************************
********************************************************************************************************
********************************************************************************************************

local sR *
local sig1 `sR'
local sig2 `sR'`sR'
local sig3 `sR'`sR'`sR'
local formatDisp %10.2f

	


*Complete sample
	foreach var of varlist `outcomes' {
        qui count
        if "`var'"=="incomedrop" loc Count: di %10.0fc r(N)
	qui sum `var' [aw=$weight] 
	loc mpost=`r(mean)'
	loc mpost_`var'_all1:  di %10.2fc r(mean)
	loc `var'_SD :  di %10.2fc r(sd)
	loc sdpr_`var'_all1 "(``var'_SD')"
	
*Heterogeneity dimensions
	
	foreach het of varlist `heterogeneity' {
	forval i=0/1 {
        qui count if `het'==`i'
        if "`var'"=="incomedrop" loc Count_`het'`i': di %10.0fc r(N)

	qui sum `var' [aw=$weight] if `het'==`i'
	loc mpost=`r(mean)'
	loc mpost_`var'_`het'`i':  di %10.2fc r(mean)
	loc `var'_SD :  di %10.2fc r(sd)
	loc sdpr_`var'_`het'`i' "(``var'_SD')"
	
	}
	}
	}
		
	clear all
	set obs 3
	
        g Group=""
        g N=""
           
		   
	g full_sample=0
	replace full_sample = 1 in 1
	replace Group = "All" if full_sample == 1
        replace N="`Count'" if full_sample == 1

	g below_consumption=0
	replace below_consumption = 1 in 2
	replace Group = "below_consumption" if below_consumption == 1
        replace N="`Count_poor1'" if below_consumption == 1

	g above_consumption=0
	replace above_consumption = 1 in 3
	replace Group = "above_consumption" if above_consumption == 1
        replace N="`Count_poor0'" if above_consumption == 1



	foreach var in `outcomes' { 

		g `var'   = ""	

			replace `var'   = "`mpost_`var'_poor1'" if below_consumption==1
			replace `var'   = "`mpost_`var'_poor0'" if above_consumption==1
			replace `var'   = "`mpost_`var'_all1'" if full_sample==1
			
	}
	
                        label var Group "Group"
                        label var N "Number of households"
                        label var employmentdrop "Drop in employment"
                        label var incomedrop "Drop in income"
                        label var incomeincr "Increase in income"
                        label var accessmarkets "Reduced access to markets"
                        label var missedmeals "Missed or reduced meals"
                        label var anysupport "Received NGO or Govt Support"
                        label var healthaccess "Delayed or skipped healthcare"

                        order Group incomedrop incomeincr employmentdrop accessmarkets healthaccess missedmeals anysupport N //divest borrow work_more_earn
                        
                            preserve
                            keep if Group=="All"
                            destring(incomedrop incomeincr), g(up down)
                            gen ratio = round(up/down, .01)
                            keep incomedrop incomeincr ratio
                            export excel using "$dtab/Inverse Table.xlsx", sheetmodify cell(B14)
                            restore
 
        drop below_consumption above_consumption full_sample Group incomeincr
                      
 	export excel using "$dtab/Main Table.xlsx", sheetmodify cell(B26)




